package com.wsj.dao.hibernate;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import net.sf.swarmcache.AutoCache;

import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.orm.hibernate3.SessionFactoryUtils;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.wsj.dao.IHomeSiteNewsDao;
import com.wsj.util.exception.BusinessException;
import com.wsj.util.page.PageResultSet;
import com.wsj.util.page.PerfectPageResultSet;
import com.wsj.util.tool.StringUtils;
import com.wsj.util.tool.Tool;
import com.wsj.web.constant.homeSiteNews.HomeSiteNewsConstant;
import com.wsj.web.subclass.displayBean.HomeSiteNews.AutoPlayFormExtendBean;
import com.wsj.web.subclass.displayBean.HomeSiteNews.HomeSiteNewsExtendBean;
import com.wsj.web.subclass.displayBean.HomeSiteNews.HomeSiteNewsFormExtendBean;
import com.wsj.web.vo.homeSite.AutoPlay;
import com.wsj.web.vo.homeSite.HomeSiteNews;
import com.wsj.web.vo.homeSite.HomeSiteNewsClass;

public class HomeSiteNewsDaoImpl extends HibernateDaoSupport  implements IHomeSiteNewsDao 
{

	 private  JdbcTemplate jdbcTemplate;
	 
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public void add(HomeSiteNews homeSiteNews) throws DataAccessException {
		this.getHibernateTemplate().save(homeSiteNews);

	}
	
	public void delete(long id) throws DataAccessException {
		HomeSiteNews homeSiteNews=(HomeSiteNews) this.getHibernateTemplate().get(HomeSiteNews.class, Long.valueOf(id));
		this.getHibernateTemplate().delete(homeSiteNews);

	}

	public HomeSiteNews getHomeSiteNewsById(long id) throws DataAccessException {
		HomeSiteNews homeSiteNews=null;
		homeSiteNews=(HomeSiteNews) this.getHibernateTemplate().get(HomeSiteNews.class, Long.valueOf(id));
		
		return homeSiteNews;
	}

	public HomeSiteNews getHomeSiteNewsByName(Long homeSiteNewsClassid,String title)
			throws DataAccessException {
		HomeSiteNews homeSiteNews=null;
		List list=new ArrayList();
		String sql=" from HomeSiteNews as bm where bm.filePath = ? and bm.homeSiteNewsClass.id = ? ";
		list=this.getHibernateTemplate().find(sql, new Object[]{title,homeSiteNewsClassid});
		if(list!=null&&!list.isEmpty()){
			homeSiteNews=(HomeSiteNews) list.get(0);
		}
		
		
		return homeSiteNews;
	}

	public PageResultSet loadHomeSiteNewsByParam(String keyword, Integer qyId,
			Integer iscommend, long personalId, String title, Integer provId,
			Integer cityId, Integer diquId, int page, int pageSize,long homeSiteNewsClassid,short isDel,String  content,String  createDatetimeStr)
			throws DataAccessException {
//		 初始化
		PageResultSet pageResultSet = new PageResultSet();

		PerfectPageResultSet pageResult = new PerfectPageResultSet();

		// 获取Session
		Session session = SessionFactoryUtils.getSession(getSessionFactory(),
				true);

		// 初始化SQL语句
		StringBuffer stringBuffer = new StringBuffer("from HomeSiteNews as bm where 1=1 ");

		// 设置每页显示得记录数和开始页
		pageResult.setPage(page, pageSize);
		
        // 参数
        List<Comparable> list = new ArrayList<Comparable>();
        if(keyword!= null&&!keyword.equals("")){
            stringBuffer.append(" and bm.keyword like ?");
            
            list.add("%"+keyword+"%");
        }
        
        //内容
        if(content!= null&&!content.equals("")){
        	stringBuffer.append(" and bm.content like ?");
        	
        	list.add("%"+content+"%");
        }
        
        //日期查询
        if(createDatetimeStr!= null&&!createDatetimeStr.equals("")){
        	
        	
        	if(createDatetimeStr.indexOf(",")==-1){
        		
        		stringBuffer.append(" and bm.createDatetime >= '"+createDatetimeStr.trim() +" 00:00:00'");
        		
        	}
        	
        	
        	if(createDatetimeStr.indexOf(",")!=-1){
        		
        		String[] strTime=createDatetimeStr.split(",");
        		if(strTime.length>1){
        			
            		String startTime=strTime[0];
            		String endTime=strTime[1];
            		if(startTime!=null&&!startTime.trim().equals("")&&endTime!=null&&!endTime.equals("")){
            			stringBuffer.append(" and bm.createDatetime >= '"+startTime.trim() +" 00:00:00'"+" and bm.createDatetime< '"+endTime.trim()+" 23:59:59'");
            		}
            		
            		if(startTime!=null&&!startTime.trim().equals("")&&(endTime==null||endTime.equals(""))){
            			stringBuffer.append(" and bm.createDatetime >= '"+startTime.trim() +" 00:00:00'");
            			
            		}
            		if((startTime==null||startTime.trim().equals(""))&&(endTime!=null&&!endTime.equals(""))){
            			stringBuffer.append(" and bm.createDatetime <= '"+endTime.trim() +" 23:59:59'");
            			
            		}
            		
            		
            		
        			
        		}
        	}
        }
        if(title!= null&&!title.equals("")){
        	stringBuffer.append(" and bm.title like ?");
        	
        	list.add("%"+title+"%");
        }
        
        
        if(provId!=null&&provId.intValue()!=0&&provId.intValue()!=-2){
        	
            stringBuffer.append(" and bm.prov.provId = ?");
            
            list.add(provId);
        	
        }
        if(cityId!=null&&cityId.intValue()!=0&&cityId.intValue()!=-2){
        	
        	stringBuffer.append(" and bm.city.cityId = ?");
        	
        	list.add(cityId);
        	
        }
        if(diquId!=null&&diquId.intValue()!=0&&diquId.intValue()!=-2){
        	
        	stringBuffer.append(" and bm.diqy.diquId =?");
        	
        	list.add(diquId);
        	
        }
        if(homeSiteNewsClassid!=0&&homeSiteNewsClassid!=-2){
        	
        	stringBuffer.append(" and bm.homeSiteNewsClass.seq like ?");
        	list.add("%,"+Long.valueOf(homeSiteNewsClassid)+",%");
        	
        }
        if(isDel!=-2){
        	
        	stringBuffer.append(" and bm.isDel =?");
        	list.add(isDel);
        	
        }
        
		stringBuffer.append(" order by bm.id desc ");
		
		 String[] strParams = Tool.toArray(list);
	        pageResultSet = pageResult.pageResultSet(stringBuffer.toString(),
	                session, strParams);

		return pageResultSet;
	}
	
	public PageResultSet loadHomeSiteNewsByExtendBean(HomeSiteNewsFormExtendBean homeSiteNewsFormExtendBean)
	throws DataAccessException {
//		 初始化
		PageResultSet pageResultSet = new PageResultSet();
		
		PerfectPageResultSet pageResult = new PerfectPageResultSet();
		
		// 获取Session
		Session session = SessionFactoryUtils.getSession(getSessionFactory(),
				true);
		
		// 初始化SQL语句
		StringBuffer stringBuffer = new StringBuffer("from HomeSiteNews as bm where 1=1 ");
		
		// 设置每页显示得记录数和开始页
		pageResult.setPage(homeSiteNewsFormExtendBean.getPage(), homeSiteNewsFormExtendBean.getPageSize());
		
		// 参数
		List<Comparable> list = new ArrayList<Comparable>();
		if(homeSiteNewsFormExtendBean.getKeyword()!= null&&!homeSiteNewsFormExtendBean.getKeyword().equals("")){
			stringBuffer.append(" and bm.keyword like ?");
			
			list.add("%"+homeSiteNewsFormExtendBean.getKeyword()+"%");
		}
		
		//内容
		if(homeSiteNewsFormExtendBean.getContent()!= null&&!homeSiteNewsFormExtendBean.getContent().equals("")){
			stringBuffer.append(" and bm.content like ?");
			
			list.add("%"+homeSiteNewsFormExtendBean.getContent()+"%");
		}
		//栏目名称
		if(homeSiteNewsFormExtendBean.getHomeSiteNewsClassName()!= null&&!homeSiteNewsFormExtendBean.getHomeSiteNewsClassName().equals("")){
			stringBuffer.append(" and bm.homeSiteNewsClass.names = ?");
			
			list.add(homeSiteNewsFormExtendBean.getHomeSiteNewsClassName());
		}
		
		//日期查询
		if(homeSiteNewsFormExtendBean.getCreateDatetimeStr()!= null&&!homeSiteNewsFormExtendBean.getCreateDatetimeStr().equals("")){
			
			
			if(homeSiteNewsFormExtendBean.getCreateDatetimeStr().indexOf(",")==-1){
				
				stringBuffer.append(" and bm.createDatetime >= '"+homeSiteNewsFormExtendBean.getCreateDatetimeStr().trim() +" 00:00:00'");
				
			}
			
			
			if(homeSiteNewsFormExtendBean.getCreateDatetimeStr()!=null && homeSiteNewsFormExtendBean.getCreateDatetimeStr().indexOf(",")!=-1){
				
				String[] strTime=homeSiteNewsFormExtendBean.getCreateDatetimeStr().split(",");
				if(strTime.length>1){
					
					String startTime=strTime[0];
					String endTime=strTime[1];
					if(startTime!=null&&!startTime.trim().equals("")&&endTime!=null&&!endTime.equals("")){
						stringBuffer.append(" and bm.createDatetime >= '"+startTime.trim() +" 00:00:00'"+" and bm.createDatetime< '"+endTime.trim()+" 23:59:59'");
					}
					
					if(startTime!=null&&!startTime.trim().equals("")&&(endTime==null||endTime.equals(""))){
						stringBuffer.append(" and bm.createDatetime >= '"+startTime.trim() +" 00:00:00'");
						
					}
					if((startTime==null||startTime.trim().equals(""))&&(endTime!=null&&!endTime.equals(""))){
						stringBuffer.append(" and bm.createDatetime <= '"+endTime.trim() +" 23:59:59'");
						
					}
					
					
					
					
				}
			}
		}
		if(homeSiteNewsFormExtendBean.getTitle()!= null&&!homeSiteNewsFormExtendBean.getTitle().equals("")){
			stringBuffer.append(" and bm.title like ?");
			
			list.add("%"+homeSiteNewsFormExtendBean.getTitle()+"%");
		}
		
		if(homeSiteNewsFormExtendBean.getHomeSiteNewsClassid()!=null&&homeSiteNewsFormExtendBean.getHomeSiteNewsClassid()!=0&&homeSiteNewsFormExtendBean.getHomeSiteNewsClassid()!=-2){
			
			stringBuffer.append(" and bm.homeSiteNewsClass.seq like ?");
			list.add("%,"+homeSiteNewsFormExtendBean.getHomeSiteNewsClassid()+",%");
			
		}
		if(homeSiteNewsFormExtendBean.getIsDel()!=-2){
			
			stringBuffer.append(" and bm.isDel =?");
			list.add(homeSiteNewsFormExtendBean.getIsDel());
			
		}
		
		stringBuffer.append(" order by bm.id desc ");
		
		String[] strParams = Tool.toArray(list);
		pageResultSet = pageResult.pageResultSet(stringBuffer.toString(),
				session, strParams);
		
		return pageResultSet;
	}

	public void update(HomeSiteNews homeSiteNews) throws DataAccessException {
		
		this.getHibernateTemplate().update(homeSiteNews);
		

	}

	/*****
	 * GBX 加 搜索用 
	 * 条件： 标题、内容、关键字 、
	 * 附加条件：已审核、删除否、推荐、时间排序
	 * */  
	public PageResultSet loadNewsByParam(String[] keywords,Integer type,Integer page, Integer pageSize) throws DataAccessException {

			// 初始化
			PageResultSet pageResultSet = new PageResultSet();
			PerfectPageResultSet pageResult = new PerfectPageResultSet();
			// 获取Session
			Session session = SessionFactoryUtils.getSession(getSessionFactory(),true);
			// 初始化SQL语句
			StringBuffer stringBuffer = new StringBuffer(" from HomeSiteNews hs where 1=1 and hs.audite = 1 and hs.isDel = 0 and ( ");

			// 设置每页显示得记录数和开始页
			pageResult.setPage(page, pageSize);
			
	        // 参数
	        List<Comparable> list = new ArrayList<Comparable>();
	        
	        // 单个关键字
	        if(keywords!=null && keywords.length==1){
	        	String str = "%"+keywords[0]+"%";
	        	// 标题+关键字
	        	if(HomeSiteNewsConstant.Type1.equals(type)){
		            stringBuffer.append(" hs.title like ? ");
		            list.add(str);
		            stringBuffer.append(" or hs.keyword like ? ");
		            list.add(str);
	        	}
	        	//  内容+关键字
	        	if(HomeSiteNewsConstant.Type2.equals(type)){
		            stringBuffer.append(" hs.content like ? ");
		            list.add(str);
		            stringBuffer.append(" or hs.keyword like ? ");
		            list.add(str);
	        	}
	        	//  标题+内容
	        	if(HomeSiteNewsConstant.Type3.equals(type)){
		            stringBuffer.append(" hs.title like ? ");
		            list.add(str);
		            stringBuffer.append(" or hs.content like ? ");
		            list.add(str);
	        	}
	        }
	        
	        //  如果多关键字
	        if(keywords!=null && keywords.length>1){
	        	
	        	// 标题+关键字
	        	if(HomeSiteNewsConstant.Type1.equals(type)){
	        		String s = "%"+keywords[0]+"%";
	        		stringBuffer.append(" hs.title like ? ");
		            list.add(s);
		            stringBuffer.append(" or hs.keyword like ? ");
		            list.add(s);
	        		for(int i=1;i<keywords.length;i++){
		        		String str = "%"+keywords[i]+"%";
		        		stringBuffer.append(" or hs.title like ? ");
			            list.add(str);
			            stringBuffer.append(" or hs.keyword like ? ");
			            list.add(str);
		        	}
		            
	        	}
	        	//  内容+关键字
	        	if(HomeSiteNewsConstant.Type2.equals(type)){
	        		String s = "%"+keywords[0]+"%";
	        		stringBuffer.append(" hs.title like ? ");
		            list.add(s);
		            stringBuffer.append(" or hs.keyword like ? ");
		            list.add(s);
	        		for(int i=1;i<keywords.length;i++){
	        			String str = "%"+keywords[i]+"%";
		        		stringBuffer.append(" or hs.content like ? ");
			            list.add(str);
			            stringBuffer.append(" or hs.keyword like ? ");
			            list.add(str);
		        	}
		            
	        	}
	        	//  标题+内容
	        	if(HomeSiteNewsConstant.Type3.equals(type)){
	        		String s = "%"+keywords[0]+"%";
	        		stringBuffer.append(" hs.title like ? ");
		            list.add(s);
		            stringBuffer.append(" or hs.content like ? ");
		            list.add(s);
	        		for(int i=1;i<keywords.length;i++){
	        			String str = "%"+keywords[i]+"%";
		        		stringBuffer.append(" or hs.title like ? ");
			            list.add(str);
			            stringBuffer.append(" or hs.content like ? ");
			            list.add(str);
		        	}
	        	}
	        }
	        
		
	        stringBuffer.append(" ) order by hs.iscommend desc,hs.updateDatetime desc ");
			
			String[] strParams = Tool.toArray(list);
			pageResultSet = pageResult.pageResultSet(stringBuffer.toString(),
					session, strParams);
			
			return pageResultSet;
	}

	
	/** 如果是图片新闻，必须有图片，否则先按时间排序，再按图片新闻排序 **/ 
	/**
	 * 主要用于首页新闻资讯的查询
	 * 基本条件：删除否、推荐、审核
	 * 条件：是否是图片新闻、资讯分类ID、前 top 条
	 * 额外条件： 是否热点 （第一条显示的要是热点）
	 * */
	@SuppressWarnings("unchecked")
	public List<HomeSiteNews> getTopByNewsClassId(Short hotspot,Integer isPic,Long newsClassId, Long[] rdNewsId, Integer top) throws DataAccessException {
		List list = null;
		StringBuffer sql= new StringBuffer(" from HomeSiteNews as h where 1=1 and h.isDel=0 and h.audite=1 and h.iscommend=1 ");
		if(newsClassId != null){
			sql.append(" and h.homeSiteNewsClass.id = :homeSiteNewsClassId ");
		}
		
		if(hotspot != null && hotspot.equals(new Short("1"))){
			sql.append(" and h.hotspot = 1 ");
		}
		
		if(isPic != null && (new Integer(1)).equals(isPic)){
			sql.append(" and h.isImg = 1");
		}
		
		//  排除热点资讯，避免重复出现
		if(rdNewsId != null){
			for(int i=0;i<rdNewsId.length;i++){
				sql.append(" and h.id != " + rdNewsId[i]);
			}
		}
		
		sql.append(" order by h.createDatetime desc,h.isImg desc ");
		
		Query query = this.getSession().createQuery(sql.toString());
		if(newsClassId != null){
			query.setLong("homeSiteNewsClassId", newsClassId);
		}
		
		list = query.setMaxResults(top).list();
		if(StringUtils.isNotNull(list)){
			return list;
		}
		return null;
	}
	
	/**
	 * 资讯列表页面
	 * 条件:资讯分类ID,审核通过、未删除
	 * 排序：时间、推荐
	 * **/ 
	public PageResultSet loadByClassId(Long classId, Integer page, Integer pageSize) throws DataAccessException {
		//		 初始化
		PageResultSet pageResultSet = new PageResultSet();
		PerfectPageResultSet pageResult = new PerfectPageResultSet();
		// 获取Session
		Session session = SessionFactoryUtils.getSession(getSessionFactory(),true);
		// 初始化SQL语句
		StringBuffer stringBuffer = new StringBuffer(" from HomeSiteNews as h where h.isDel = 0  and h.audite = 1");

		// 设置每页显示得记录数和开始页
		pageResult.setPage(page, pageSize);
		
        // 参数
        List<Comparable> list = new ArrayList<Comparable>();
		if(classId != null){
			stringBuffer.append(" and h.homeSiteNewsClass.id = ? ");
			list.add(classId);
		}		
		
		stringBuffer.append(" order by h.createDatetime desc,h.iscommend desc");
			
		String[] strParams = Tool.toArray(list);
		
		pageResultSet = pageResult.pageResultSet(stringBuffer.toString(),session, strParams);
			
		return pageResultSet;
	}
	
	/**
	 * 根据省份ID 、城市ID 加载资讯
	 * */
	public PageResultSet loadByProvOrCityId(Integer qyId,Integer provId, Integer cityId, Long cid,Integer page, Integer pageSize) throws DataAccessException {
		//		 初始化
		PageResultSet pageResultSet = new PageResultSet();
		PerfectPageResultSet pageResult = new PerfectPageResultSet();
		// 获取Session
		Session session = SessionFactoryUtils.getSession(getSessionFactory(),true);
		// 初始化SQL语句
		StringBuffer stringBuffer = new StringBuffer(" from HomeSiteNews as h where h.isDel = 0  and h.audite = 1");

		// 设置每页显示得记录数和开始页
		pageResult.setPage(page, pageSize);
		
        // 参数
        List<Comparable> list = new ArrayList<Comparable>();
		if(provId != null){
			stringBuffer.append(" and h.prov.provId = ? ");
			list.add(provId);
		}		
		if(cityId != null){
			stringBuffer.append(" and h.city.cityId = ? ");
			list.add(cityId);
		}
		if(qyId != null){
			stringBuffer.append(" and h.qyId = ? ");
			list.add(qyId);
		}
		if(cid != null){
			stringBuffer.append(" and h.homeSiteNewsClass.id = ? ");
			list.add(cid);
		}
		stringBuffer.append(" order by h.createDatetime desc,h.iscommend desc");
			
		String[] strParams = Tool.toArray(list);
		pageResultSet = pageResult.pageResultSet(stringBuffer.toString(),session, strParams);
			
		return pageResultSet;
	}
	
	public List getTopHSiteNewsListByNewsClassId(long newsClassId, int top) throws DataAccessException {
		String sql=" from HomeSiteNews as h where 1=1 and h.isOutLink=1  and h.homeSiteNewsClass.id = ?  order by h.id desc";
		
		Query q=this.getSession().createQuery(sql);
		q.setParameter(0, newsClassId);
		q.setFirstResult(0);
		q.setMaxResults(top);
		return q.list();
	}

	public List getTopHSiteNByOnlyNewId(long newsClassId, int top) throws DataAccessException {
		String sql=" from HomeSiteNews as h where 1=1 and h.isDel=0 and h.audite=1 and h.homeSiteNewsClass.id = ?  order by h.id desc";
		
		Query q=this.getSession().createQuery(sql);
		q.setParameter(0, newsClassId);
		q.setFirstResult(0);
		q.setMaxResults(top);
		return q.list();
	}

	/***
	 * 根据 新闻点击率获取 前 top 新闻，若 top 为空，获取默认的前 20条
	 **/
	public List loadTopByHits(Integer top) throws DataAccessException {
		String sql=" from HomeSiteNews as h where h.isDel=0 and h.audite=1 order by h.hits desc,h.createDatetime desc";
		Query q=this.getSession().createQuery(sql);
		if(top != null ){
			q.setMaxResults(top);
		}else{
			q.setMaxResults(20);
		}
		List list = q.list();
		if(StringUtils.isNotNull(list)){
			return list;
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public List<HomeSiteNews> getTopBykeyword(String keyword, Integer top) throws DataAccessException {
		
		String sql=" from HomeSiteNews as h where 1=1 and h.isDel=0 and h.audite=1 and h.keyword like ? order by h.id desc";
		Query q=this.getSession().createQuery(sql);
		q.setParameter(0, "%"+keyword+"%");
		q.setFirstResult(0);
		q.setMaxResults(top);
		return q.list();
	}

	@SuppressWarnings("unchecked")
	public List<HomeSiteNews> getTopByclassId(Short hotspot,Integer provId, Integer top) throws BusinessException {
		StringBuffer sql = new StringBuffer(" from HomeSiteNews as h where h.isDel=0 and h.audite=1 ");
		if(provId != null){
			sql.append(" and h.prov.provId = :provId ");
		}
		sql.append(" order by h.iscommend desc,h.createDatetime desc ");
		Query q=this.getSession().createQuery(sql.toString());
		if(provId != null){
			q.setInteger("provId", provId);
		}
		if(hotspot != null && hotspot.equals(new Short("1"))){
			sql.append(" and h.hotspot = 1 ");
		}
		
		List list = q.setMaxResults(top).list();
		if(StringUtils.isNotNull(list)){
			return list;
		}
		return null;		
	}

	@SuppressWarnings("unchecked")
	/**
	 * 总站首页：各地市场
	 * 条件：删除否、审核、图片是 
	 * 并且是属于 “石家庄、常州、无锡、武汉”的资讯
	 * */
	public List<HomeSiteNews> getTopByclassId(Short hotspot,Integer id1,Integer id2,Integer id3,Integer id4, Short isPic, Integer top) throws BusinessException {
		StringBuffer sql = new StringBuffer(" from HomeSiteNews as h where h.isDel=0 and h.audite=1 and (");
		sql.append(" h.city.cityId = ? or h.city.cityId = ?  or h.city.cityId = ?  or h.city.cityId = ? ");
		
		if(isPic != null && new Short("1").equals(isPic)){
			sql.append(" and h.isImg = 1 and h.img is not null");
		}
		if(hotspot != null && hotspot.equals(new Short("1"))){
			sql.append(" and h.hotspot = 1 ");
		}
		sql.append(" ) order by h.iscommend desc,h.createDatetime desc ");
		Query q=this.getSession().createQuery(sql.toString());
		q.setInteger(0,id1);
		q.setInteger(1,id2);
		q.setInteger(2,id3);
		q.setInteger(3,id4);
		
		List list = q.setMaxResults(top).list();
		if(StringUtils.isNotNull(list)){
			return list;
		}
		return null;
	}
	
	
	/*****
	 * 通过栏目名等查询前几个资讯
	 */
	public List loadNewsByClassNameListDao(String name, Short picOrChar, Short isdel, Short audite, Short isCommend, Integer top) throws DataAccessException 
	{
		StringBuffer hql=new StringBuffer(" from HomeSiteNews as n where n.homeSiteNewsClass.names = ? ");
		if(picOrChar!=null)
		{
			hql.append(" and n.isImg = ?");
		}
		
		if(isdel!=null)
		{
			hql.append(" and n.isDel = ?");
		}
		
		if(audite!=null)
		{
			hql.append(" and n.audite = ?");
		}
		
		if(isCommend!=null)
		{
			hql.append(" and n.iscommend = ?");
		}
		hql.append(" order by n.createDatetime desc");
		
		Query q=this.getSession().createQuery(hql.toString());
		q.setParameter(0, name);
		if(picOrChar!=null)
		{
			q.setParameter(1,picOrChar);
		}
		
		if(isdel!=null)
		{
			q.setParameter(2, isdel);
		}
		
		if(audite!=null)
		{
		   q.setParameter(3, audite);	
		}
		
		if(isCommend!=null)
		{
			q.setParameter(4, isCommend);
		}
		q.setFirstResult(0);
		q.setMaxResults(top);
		
		return q.list();
		
		
	}

	public HomeSiteNews getTopHSiteNByOrderBytime(long newsClassId, Integer id,Integer top) throws DataAccessException {
		HomeSiteNews homeSiteNews=null;
		String sql=" from HomeSiteNews as h where 1=1 and h.isDel=0 and h.audite=1 and h.homeSiteNewsClass.id = ? and h.qyId = ?  order by h.createDatetime desc";
		Query q=this.getSession().createQuery(sql);
		q.setParameter(0, newsClassId);
		q.setParameter(1, id);
		q.setFirstResult(0);
		q.setMaxResults(top);
		List list=q.list();
		if(list!=null&&!list.isEmpty()&&list.size()>0){
			homeSiteNews=(HomeSiteNews) list.get(0);
		}
		return homeSiteNews;
	}

	/*****
	 * 先获取市场行情分类下面的子分类,然后再获取子分类下面的资讯列表
	 */
	@SuppressWarnings("unchecked")
	public List<HomeSiteNews> loadSCHQClassNewsListDao(Integer provId,Integer cityId, Short hot, Short pic, Long id, Integer index_top_fifteen) throws DataAccessException 
	{
		String hql="  from HomeSiteNewsClass as c where c.names = ?";
		List li = this.getHibernateTemplate().find(hql,"市场行情");
		HomeSiteNewsClass homeSiteNewsClass = new HomeSiteNewsClass();
		if(li!=null  && !li.isEmpty())
		{
			 homeSiteNewsClass = (HomeSiteNewsClass)li.get(0);
		}
		
		List schqList = this.getHibernateTemplate().find("  from HomeSiteNewsClass as c where c.parenId = ?",homeSiteNewsClass.getId());
		
		String  ids="";
//		先获取市场行情 下面所有的子分类
		if(schqList!=null  && !schqList.isEmpty())
		{
			StringBuffer buf=new StringBuffer("");
			for(int j=0;j<schqList.size();j++)
			{
				if(j==0)
				{
					HomeSiteNewsClass homeSiteNewsClass2 = (HomeSiteNewsClass)schqList.get(j);
					buf.append(homeSiteNewsClass2.getId());
				}
				else
				{
					HomeSiteNewsClass homeSiteNewsClass2 = (HomeSiteNewsClass)schqList.get(j);
					buf.append(",").append(homeSiteNewsClass2.getId());
				}
			}
			ids=buf.toString();
		}
		
//		String sql=" from HomeSiteNews as h where 1=1 and  h.homeSiteNewsClass.id  in (?)";
//		Query q=this.getSession().createQuery(sql);
//		q.setParameter(0, Long.valueOf(ids));
//		q.setFirstResult(0);
//		q.setMaxResults(index_top_fifteen);
//		List list=q.list();
		
		
		StringBuffer sql=new StringBuffer("select top ").append(index_top_fifteen)
		.append(" h.id as id,h.img as img ,h.filePath as filePath, h.title  as title,h.content as content,hc.id as hc_id,hc.names as names");
		sql.append(" from homeSite_news as h join homeSite_news_class as hc on h.homeSite_news_class_id = hc.id  where 1=1 ");
		sql.append(" and hc.id in ("+ids+")");
		sql.append(" order by h.create_datetime desc");
		final List list = new ArrayList();
  		
 		 this.getJdbcTemplate().query(sql.toString(),new ResultSetExtractor(){
 	          
 	          @SuppressWarnings("unchecked")
			public Object extractData(ResultSet rs) throws SQLException,DataAccessException 
 	          {
 	             while(rs.next())
 	             {
 	            	HomeSiteNews homeSiteNews  =new HomeSiteNews();  
 	            	HomeSiteNewsClass  homeSiteNewsClass = new HomeSiteNewsClass();
 	            	homeSiteNewsClass.setId(rs.getLong("hc_id"));
 	            	homeSiteNewsClass.setNames(rs.getString("names"));
 	            	homeSiteNews.setHomeSiteNewsClass(homeSiteNewsClass);
 	            	homeSiteNews.setId(rs.getLong("id"));
 	            	homeSiteNews.setImg(rs.getString("img"));
 	            	homeSiteNews.setFilePath(rs.getString("filePath"));
 	            	homeSiteNews.setTitle(rs.getString("title"));
 	            	homeSiteNews.setContent(rs.getString("content"));
 	                 list.add(homeSiteNews);
 	             }
 	             return list;
 	          }
 	      });
 	        
		return list;
		
	}

	
	/*****
	 * 获取分站点击量最大的资讯列表
	 */
	@SuppressWarnings("unchecked")
	public List<HomeSiteNews> loadTopByHitsDao(Integer provId,Integer cityId, Integer top) throws DataAccessException
	{
	
		StringBuffer hql=new StringBuffer(" from HomeSiteNews as n where 1=1");
		if(provId!=null)
		{
			hql.append(" and n.prov.provId = ?");
			
		}
		
		if(cityId!=null)
		{
			hql.append(" and  n.city.cityId=?");
		}
		
		hql.append(" order by n.hits desc");
		
		
		Query q=this.getSession().createQuery(hql.toString());
		Integer param=0;
		if(provId!=null)
		{
			q.setParameter(param, provId);
			param = param +1;
		}
		
		if(cityId!=null)
		{
			q.setParameter(param, cityId);
			param = param +1;
		}
		q.setFirstResult(0);
		q.setMaxResults(top);
		
		return q.list();
	}

	
	/******
	 * 获取某个分类下面的资讯列表
	 */
	@SuppressWarnings("unchecked")
	public List<HomeSiteNews> loadTopByclassIdDao(Integer provId,Integer cityId, Short hotspot, Integer isPic, Long newsClassId, Integer top) throws DataAccessException 
	{
	    StringBuffer hql=new StringBuffer("from HomeSiteNews as n where 1=1");
	    
	    
	    if(provId!=null)
	    {
	    	hql.append(" and n.prov.provId = ?");
	    }
	    
	    if(cityId!=null)
	    {
	    	hql.append(" and n.city.cityId = ?");
	    }
	    
	    if(hotspot!=null)
	    {
	    	hql.append(" and n.hotspot = ?");
	    }
	    
	    if(isPic!=null)
	    {
	    	hql.append(" and n.isPic = ?");
	    }
	    
	    if(newsClassId!=null)
	    {
	    	hql.append(" and n.homeSiteNewsClass.id = ?");
	    }
	    
	    hql.append(" order by n.createDatetime desc");
	    Query q=this.getSession().createQuery(hql.toString());
	    Integer parem=0;
	    if(provId!=null)
	    {
	    	q.setParameter(parem, provId);
	    	parem=parem+1;
	    }
	    
	    if(cityId!=null)
	    {
	    	q.setParameter(parem, cityId);
	    	parem=parem+1;
	    }
	    
	    if(hotspot!=null)
	    {
	    	q.setParameter(parem, hotspot);
	    	parem=parem+1;
	    }
	    
	    if(isPic!=null)
	    {
	    	q.setParameter(parem, isPic);
	    	parem=parem+1;
	    }
	    
	    if(newsClassId!=null)
	    {
	    	q.setParameter(parem, newsClassId);
	    	parem=parem+1;
	    }
	    
	    q.setFirstResult(0);
		q.setMaxResults(top);
		
		return q.list();
		  
	}

	public List loadHomeSiteNews(HomeSiteNewsFormExtendBean exbean) throws DataAccessException {
		StringBuffer hql=new StringBuffer(" from HomeSiteNews as h where 1=1 ");
		
		//推荐
		if(exbean.getIscommend()!=null && exbean.getIscommend().intValue()!=0)
		{
			hql.append(" and h.iscommend = ?");
			
		}
		//删除
		if(exbean.getIsDel()!=0)
		{
			hql.append(" and  h.isDel = ?");
		}
		//审核
		if(exbean.getAudite()!=0)
		{
			hql.append(" and  h.audite = ?");
		}
		//图片
		if(exbean.getIsImg()!=0)
		{
			hql.append(" and  h.isImg = ?");
			hql.append(" and  h.img != null and h.isOutLink =1 and h.outLink != null ");
		}
		//栏目
		if(exbean.getHomeSiteNewsClassid()!=null && exbean.getHomeSiteNewsClassid().intValue()!=0)
		{
			hql.append(" and  h.homeSiteNewsClass.id = ?");
		}
		//省份
		if(exbean.getProvId()!=null && exbean.getProvId().intValue()!=0)
		{
			hql.append(" and  h.prov.provId = ?");
		}
		//城市
		if(exbean.getCityId()!=null && exbean.getCityId().intValue()!=0)
		{
			hql.append(" and  h.city.cityId = ?");
		}
		//qiid
		if(exbean.getQyId()!=null && exbean.getQyId().intValue()!=0)
		{
			hql.append(" and  h.qyId = ?");
		}
		
		hql.append(" order by h.id desc");
		
		
		Query q=this.getSession().createQuery(hql.toString());
		Integer param=0;
//		推荐
		if(exbean.getIscommend()!=null && exbean.getIscommend().intValue()!=0)
		{
			q.setParameter(param, exbean.getIscommend());
			param = param +1;
		}
//		删除
		if(exbean.getIsDel()!=0)
		{
			q.setParameter(param, exbean.getIsDel());
			param = param +1;
		}
//		审核
		if(exbean.getAudite()!=0)
		{
			q.setParameter(param, exbean.getAudite());
			param = param +1;
		}
		//图片
		if(exbean.getIsImg()!=0)
		{
			q.setParameter(param, exbean.getIsImg());
			param = param +1;
		}
		//栏目
		if(exbean.getHomeSiteNewsClassid()!=null && exbean.getHomeSiteNewsClassid().intValue()!=0)
		{
			q.setParameter(param, exbean.getHomeSiteNewsClassid());
			param = param +1;
		}
		//省份
		if(exbean.getProvId()!=null && exbean.getProvId().intValue()!=0)
		{
			q.setParameter(param, exbean.getProvId());
			param = param +1;
		}
		//城市
		if(exbean.getCityId()!=null && exbean.getCityId().intValue()!=0)
		{
			q.setParameter(param, exbean.getCityId());
			param = param +1;
		}
		//qiyeid
		if(exbean.getQyId()!=null && exbean.getQyId().intValue()!=0)
		{
			q.setParameter(param, exbean.getQyId());
			param = param +1;
		}
		q.setFirstResult(0);
		q.setMaxResults(exbean.getPage());
		
		return q.list();
	}

	public List loadHomeSiteNewsByProvOrCityAndClassIds(HomeSiteNewsExtendBean homeExtend) throws DataAccessException {
		// TODO Auto-generated method stub
		List list=new ArrayList();
		StringBuffer buff=new StringBuffer(" from HomeSiteNews as h where 1=1 and h.iscommend=1 and h.isDel=0 and h.audite=1 ");
		//省份
		if(homeExtend.getProvId()!=null && homeExtend.getProvId().intValue()!=0)
		{
			buff.append(" and  h.prov.provId = ?");
		}
		//城市
		if(homeExtend.getCityId()!=null && homeExtend.getCityId().intValue()!=0)
		{
			buff.append(" and  h.city.cityId = ?");
		}
		//图片
		if(homeExtend.getIsImg()==1)
		{
			buff.append(" and  h.img!=null and h.isImg= 1");
		}
		//去除 特处的 "1"条记录
		if(homeExtend.getSameId()!=null && homeExtend.getSameId().intValue()!=0)
		{
			buff.append(" and  h.id !=").append(homeExtend.getSameId());
		}
		//栏目
		if(homeExtend.getHomeSiteNewsClassIds()!=null 
				&& !homeExtend.getHomeSiteNewsClassIds().equals("")
				&& homeExtend.getHomeSiteNewsClassIds().indexOf(",")==-1)
		{
			buff.append(" and  h.homeSiteNewsClass.id = ? ");
		}
		else if(homeExtend.getHomeSiteNewsClassIds()!=null 
				&& !homeExtend.getHomeSiteNewsClassIds().equals("")
				&& homeExtend.getHomeSiteNewsClassIds().indexOf(",")!=-1)
		{
			String[] ids=homeExtend.getHomeSiteNewsClassIds().split(",");
		     for(int j=0;j<ids.length;j++)
		     {
		    	 if(j==0)
		    	 {
		    		 buff.append(" and  h.homeSiteNewsClass.id = ? ");
		    	 }
		    	 //最后
		    	 else if(j==ids.length-1)
		    	 {
		    		 buff.append(" or  h.homeSiteNewsClass.id = ? ");
		    	 }
		    	 //中间
		    	 else
		    	 {
		    		 buff.append(" or  h.homeSiteNewsClass.id = ? ");
		    	 }
		     }
		}
		if(homeExtend.getHits()!=null && homeExtend.getHits().intValue()==1)
		{
			buff.append(" order by h.hits desc , h.id desc");
		}
		else
		{
			buff.append(" order by h.id desc ");
		}
		Query q=this.getSession().createQuery(buff.toString());
		Integer param=0;
		//省份
		if(homeExtend.getProvId()!=null && homeExtend.getProvId().intValue()!=0)
		{
			q.setParameter(param, homeExtend.getProvId());
			param = param +1;
		}
		//城市
		if(homeExtend.getCityId()!=null && homeExtend.getCityId().intValue()!=0)
		{
			q.setParameter(param, homeExtend.getCityId());
			param = param +1;
		}
		//栏目
		if(homeExtend.getHomeSiteNewsClassIds()!=null 
				&& !homeExtend.getHomeSiteNewsClassIds().equals("")
				&& homeExtend.getHomeSiteNewsClassIds().indexOf(",")==-1)
		{
			q.setParameter(param, Long.valueOf(homeExtend.getHomeSiteNewsClassIds()));
			param = param +1;
		}
		else if(homeExtend.getHomeSiteNewsClassIds()!=null 
				&& !homeExtend.getHomeSiteNewsClassIds().equals("")
				&& homeExtend.getHomeSiteNewsClassIds().indexOf(",")!=-1)
		{
			String[] ids=homeExtend.getHomeSiteNewsClassIds().split(",");
		     for(int j=0;j<ids.length;j++)
		     {
		    	    q.setParameter(param, Long.valueOf(ids[j]));
					param = param +1;
		     }
		}
		q.setMaxResults(homeExtend.getTop());
		return q.list();
	}

	
	/************
	 * 获取推荐的新闻
	 */
	public List<HomeSiteNews> getCommendHomeSiteNewsList(int top) throws DataAccessException {
		
		Query query = this.getSession().createQuery("from HomeSiteNews h where h.iscommend = 1 and h.isDel=0 order by h.createDatetime desc");  
		List list = query.setMaxResults(top).list(); 
		return list;
		
	}

	/**********
	 * 获取该类目下,推荐的新闻
	 */
	public List<HomeSiteNews> getCommendHomeSiteNewsListByClassId(Long id, int top) throws DataAccessException {
		
		Query query = this.getSession().createQuery("from HomeSiteNews h where h.homeSiteNewsClass.id =" + id + " and  h.iscommend = 1 and h.isDel=0 order by h.createDatetime desc ");  
		List list = query.setMaxResults(top).list(); 
		return list;
	}

	/*********
	 * 获取前几条相关新闻
	 */
	public List<HomeSiteNews> getHomeSiteNewsList(int top) throws DataAccessException {
		Query query = this.getSession().createQuery("from HomeSiteNews h where  h.isDel=0 order by h.createDatetime desc");  
		List list = query.setMaxResults(top).list(); 
		return list;
	}

	 /**********
	  * 通过关键字,获取相关新闻
	  */
	public List<HomeSiteNews> getInterrelatedHomeSiteNewsList(String keyWords, int top) throws DataAccessException {
		Query query = this.getSession().createQuery("from HomeSiteNews h where  h.keyword like ?  and h.isDel=? order by h.createDatetime desc" );  
		query.setString(0, "%"+keyWords+"%");
		query.setShort(1, new Short("0"));
		List list = query.setMaxResults(top).list(); 
		return list;
	}

	
	/************
	 * 获取引导阅读的新闻,优先查询带图片的新闻,按时间倒序排列
	 * 
	 */
	public List<HomeSiteNews> getHotCommendNewsListByClassId(Short hot_news_yes, Integer commend_news_yes, Long id, int top) throws DataAccessException {
		
		StringBuffer buf = new StringBuffer("from HomeSiteNews h where 1=1 ");
		if(hot_news_yes!=null){
			buf.append(" and h.hotspot="+hot_news_yes);
		}
		
		if(commend_news_yes!=null){
			buf.append(" and h.iscommend="+commend_news_yes);
		}
		
		if(id!=null){
			buf.append(" and h.homeSiteNewsClass.id="+id);
		}
		
		
		buf.append("  and h.isDel=0  and h.iscommend = 1 and h.audite = 1 " );
		buf.append(" order by  h.createDatetime desc , h.isImg desc ");
		
		Query query = this.getSession().createQuery(buf.toString());  
		List list = query.setMaxResults(top).list(); 
		return list;
	}

	
	/*************
	 * 获取图片新闻列表
	 */
	public List<HomeSiteNews> getTopPicNewsByclassId(Short hot_news_no, Integer commend_news_yes, Long id, int top) throws DataAccessException {
		
		StringBuffer buf = new StringBuffer("from HomeSiteNews h where 1=1 ");
		if(hot_news_no!=null){
			buf.append(" and h.hotspot="+hot_news_no);
		}
		
		if(commend_news_yes!=null){
			buf.append(" and h.iscommend="+commend_news_yes);
		}
		
		if(id!=null){
			buf.append(" and h.homeSiteNewsClass.id="+id);
		}
		
		buf.append("  and  h.isImg = 1");
		buf.append("  and h.isDel=0  and h.iscommend = 1 and h.audite = 1 " );
		buf.append(" order by  h.createDatetime desc  ");
		
		Query query = this.getSession().createQuery(buf.toString());  
		List list = query.setMaxResults(top).list(); 
		return list;
	}

	
	/**********
	 * 今天推荐 置顶 推荐
	 */
	public List<HomeSiteNews> getTodayTopTitle(int top) throws DataAccessException {
		
		StringBuffer buf = new StringBuffer("from HomeSiteNews h where 1=1 ");
	    buf.append(" and h.toptitle=1");
	    buf.append("  and h.isDel=0  and h.iscommend = 1 and h.audite = 1 " );
	    buf.append(" order by h.createDatetime desc " );
	    
	    Query query = this.getSession().createQuery(buf.toString());  
		List list = query.setMaxResults(top).list(); 
		return list;
			
	}

	
	
	/************
	 * 获取一周推荐新闻列表 和一月推荐新闻列表
	 */
	public List<HomeSiteNews> getCommentNewsCollectList(long classId, int top) throws DataAccessException {
		
		StringBuffer buf = new StringBuffer("from HomeSiteNews h where 1=1 ");
	    buf.append(" and h.homeSiteNewsClass.id = " + classId);
	    buf.append(" and h.isDel=0  and h.iscommend = 1 and h.audite = 1 " );
	    buf.append(" order by h.createDatetime desc " );
	    
	    Query query = this.getSession().createQuery(buf.toString());  
		List list = query.setMaxResults(top).list(); 
		return list;
	}

	/*********
	 * 添加自动播放新闻对象
	 */
	public boolean addAutoPlay(AutoPlay autoPlay) throws DataAccessException {
		
		boolean isok=false;
		this.getHibernateTemplate().save(autoPlay);
		return isok;
	}

	
	/*******
	 * 通过id,获取自动播放对象
	 */
	public AutoPlay getAutoPlayById(Integer id) throws DataAccessException {
		
		 List li = this.getHibernateTemplate().find(" from AutoPlay ap where ap.id= ?",id);
		 if(li!=null  && !li.isEmpty()){
			 return (AutoPlay)li.get(0);
		 }
		 
		 return null;
	}

	
	/**********
	 * 获取列表
	 */
	public PageResultSet loadAutoPlayList(
			AutoPlayFormExtendBean autoPlayFormExtendBean)
			throws DataAccessException {
		
		
		PageResultSet pageResultSet = new PageResultSet();
		PerfectPageResultSet pageResult = new PerfectPageResultSet();
		// 获取Session
		Session session = SessionFactoryUtils.getSession(getSessionFactory(),true);
		// 初始化SQL语句
		StringBuffer stringBuffer = new StringBuffer(" from AutoPlay as ap where 1=1");

		// 设置每页显示得记录数和开始页
		pageResult.setPage(autoPlayFormExtendBean.getPage(), autoPlayFormExtendBean.getPageSize());
		
        // 参数
        List<Comparable> list = new ArrayList<Comparable>();
		if(autoPlayFormExtendBean.getId() != null && autoPlayFormExtendBean.getId()!=0){
			stringBuffer.append(" and ap.id = ? ");
			list.add(autoPlayFormExtendBean.getId());
		}
		
		if(autoPlayFormExtendBean.getKeyWord()!=null  && !autoPlayFormExtendBean.getKeyWord().equals("")){
			stringBuffer.append(" and ap.title  like ? ");
			list.add("%"+autoPlayFormExtendBean.getKeyWord()+"%");
		}
		
		stringBuffer.append(" order by ap.gmtModified desc");
			
		String[] strParams = Tool.toArray(list);
		pageResultSet = pageResult.pageResultSet(stringBuffer.toString(),session, strParams);
			
		return pageResultSet;
	}

	
	/***********
	 * 更新自动播放新闻
	 */
	public boolean updateAutoPlay(AutoPlay autoPlay) throws DataAccessException {
		
		  boolean isok=false;
		  
		  this.getHibernateTemplate().update(autoPlay);
		  
		  isok=true;
		  
		  return isok;
		 
	}

	
	/******
	 * 删除自动播放对象
	 */
	public void delAutoPlayById(Integer id) throws DataAccessException {
		
		  if(id!=null  && id>0){
			  AutoPlay autoPlay =this.getAutoPlayById(id);
			  this.getHibernateTemplate().delete(autoPlay);
		  }
		
	}

	
	/*********
	 * 获取前几个自动播放列表
	 */
	public List<AutoPlay> getAutoPlayNewsList(int top)
			throws DataAccessException {
		
		  String sql=" from AutoPlay as ap where 1=1 and ap.status!=-1  order by ap.gmtModified  desc limit "+ top;
		  return  (List<AutoPlay>)this.getHibernateTemplate().find(sql);
	}



}





